﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
    public class DAO_KhuyenMai
    {

        public DTO_KhuyenMai GetDetail(int makm)
        {

            string queryGetDetails = "SELECT MaKM, LoaiKM, TenKM, DonGiaCu, DonGiaMoi, BatDauKM, KetThucKM, MoTa, DiemNoiBat, DKSuDung, GioiThieuChiTiet, DoanhNghiep, HinhAnh, ClipQuangCao, HinhThucGiaoSP, TinhTrang FROM KHUYENMAI WHERE MaKM = " + makm.ToString();
            DataTable dt = DataProvider.ExecuteQuery(queryGetDetails);
            DataRow dr = dt.Rows[0];
            
                DTO_KhuyenMai dto_product = new DTO_KhuyenMai();
                dto_product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                dto_product.LoaiKM = int.Parse(dr[1].ToString());   //Loai KM
                dto_product.TenKM = dr[2].ToString();   //Tên KM
                dto_product.DonGiaCu = int.Parse(dr[3].ToString());   //Đơn giá cũ
                dto_product.DonGiaMoi = int.Parse(dr[4].ToString());   //Đơn giá mới
                dto_product.BatDauKM = DateTime.Parse(dr[5].ToString());
                dto_product.KetThucKM = DateTime.Parse(dr[6].ToString());
                dto_product.MoTa = dr[7].ToString();
                dto_product.DiemNoiBat = dr[8].ToString();
                dto_product.DKSuDung = dr[9].ToString();
                dto_product.GioiThieuChiTiet = dr[10].ToString();
                dto_product.DoanhNghiep = int.Parse(dr[11].ToString());
                dto_product.HinhAnh = dr[12].ToString();   //Link hình ảnh
                dto_product.ClipQuangCao = dr[13].ToString(); //Clip QC
                dto_product.HinhThucGiaoSP = int.Parse(dr[14].ToString());
                dto_product.TinhTrang = int.Parse(dr[15].ToString());
            return dto_product;
        }
        public List<DTO_HinhAnh> getListImages(int makm)
        {
            List<DTO_HinhAnh> list = new List<DTO_HinhAnh>();
            string query = "Select KhuyenMai, HinhAnh from HinhAnh where KhuyenMai=" + makm.ToString();
            DataTable dt = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_HinhAnh anh = new DTO_HinhAnh();                
                anh.KhuyenMai = int.Parse(dr[0].ToString()); 
                anh.HinhAnh=dr[1].ToString();
                list.Add(anh);
            }
            return list;
        }
        public int getSoLuongDatHang(int maKM)
        {
            string query="SELECT COUNT(*) FROM DONHANG WHERE KhuyenMai="+maKM.ToString();
            DataTable dt = DataProvider.ExecuteQuery(query);
            if (dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                DataRow dr = dt.Rows[0];
                return int.Parse(dr[0].ToString());
            }
        }
        public string GetTenKM(int maKM)
        {
            string querySearch = "SELECT TenKM FROM KHUYENMAI where MaKM = " + maKM.ToString();
            DataTable dt = DataProvider.ExecuteQuery(querySearch);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                DataRow dr = dt.Rows[0];
                return dr[0].ToString();
            }
        }
        public List<DTO_KM> GetDSTinKMTheoLoai(int loaiKM)
        {
            List<DTO_KM> dto_AllProducts = new List<DTO_KM>();
            string querySelectAll = "SELECT dn.TenDoanhNghiep, km.TenKM, km.BatDauKM, km.KetThucKM, km.LoaiKM, km.TinhTrang, km.MaKM FROM KHUYENMAI km, DOANHNGHIEP dn WHERE km.DoanhNghiep = dn.MaDN and km.LoaiKM = " + loaiKM.ToString();
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KM dto_product = new DTO_KM();
                dto_product.Stt = i + 1;
                dto_product.DoanhNghiep = dr[0].ToString();   //Doanh Nghiep
                dto_product.TenKM = dr[1].ToString();   //TenKM
                dto_product.BatDauKM = DateTime.Parse(dr[2].ToString());   //Ngày BD
                dto_product.KetThucKM = DateTime.Parse(dr[3].ToString());   //Ngày KT
                
                int LoaiKM = int.Parse(dr[4].ToString());             
                dto_product.LoaiKM = convertLoaiKM(LoaiKM);   //Loại KM
                int Tinhtrang = int.Parse(dr[5].ToString());
                if (Tinhtrang == 0)
                    dto_product.TinhTrang = false;
                else
                    dto_product.TinhTrang = true;
                //dto_product.TinhTrang = convertTrangThai(Tinhtrang);  //Tình Trạng 
                dto_product.MaKM = int.Parse(dr[6].ToString());
                dto_AllProducts.Add(dto_product);
                i++;
            }
            return dto_AllProducts;
        }
        public bool deleteTinKM(int ma)
        {
            string query = "Update KHUYENMAI set TinhTrang=1 where MaKM=" + ma.ToString();
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ex)
            {
                return false; ;
            }
        }
        public List<DTO_KM> GetDSTinKM()
        {
            List<DTO_KM> dto_AllProducts = new List<DTO_KM>();
            string querySelectAll = "SELECT dn.TenDoanhNghiep, km.TenKM, km.BatDauKM, km.KetThucKM, km.LoaiKM, km.TinhTrang, km.MaKM FROM KHUYENMAI km, DOANHNGHIEP dn WHERE km.DoanhNghiep = dn.MaDN"; 
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KM dto_product = new DTO_KM();
                dto_product.Stt = i + 1;
                dto_product.DoanhNghiep = dr[0].ToString();   //Doanh Nghiep
                dto_product.TenKM = dr[1].ToString();   //TenKM
                dto_product.BatDauKM = DateTime.Parse(dr[2].ToString());   //Ngày BD
                dto_product.KetThucKM = DateTime.Parse(dr[3].ToString());   //Ngày KT

                int LoaiKM = int.Parse(dr[4].ToString());
                dto_product.LoaiKM = convertLoaiKM(LoaiKM);   //Loại KM
                int Tinhtrang = int.Parse(dr[5].ToString());
                if (Tinhtrang == 0)
                    dto_product.TinhTrang = false;
                else
                    dto_product.TinhTrang = true;
                //dto_product.TinhTrang = convertTrangThai(Tinhtrang);  //Tình Trạng 
                dto_product.MaKM = int.Parse(dr[6].ToString());
                dto_product.MaKM = int.Parse(dr[6].ToString());
                dto_AllProducts.Add(dto_product);
                i++;
            }
            return dto_AllProducts;
        }
        public List<DTO_KhuyenMai> SelectAll()
        {
            List<DTO_KhuyenMai> dto_AllProducts = new List<DTO_KhuyenMai>();
            string querySelectAll = "SELECT [MaKM], [TenKM], [DonGiaCu], [DonGiaMoi], [HinhAnh] FROM KHUYENMAI";
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KhuyenMai dto_product = new DTO_KhuyenMai();
                dto_product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                dto_product.TenKM = dr[1].ToString();   //Tên KM
                dto_product.DonGiaCu = int.Parse(dr[2].ToString());   //Đơn giá cũ
                dto_product.DonGiaMoi = int.Parse(dr[3].ToString());   //Đơn giá mới
                dto_product.HinhAnh = dr[4].ToString();   //Link hình ảnh
                dto_AllProducts.Add(dto_product);
            }
            return dto_AllProducts;
        }
        public List<DTO_KhuyenMai> SelectAll(int makv)
        {
            List<DTO_KhuyenMai> dto_AllProducts = new List<DTO_KhuyenMai>();
            string querySelectAll = "SELECT [MaKM], [TenKM], [DonGiaCu], [DonGiaMoi], [HinhAnh] FROM KHUYENMAI WHERE KhuVuc="+makv.ToString();
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KhuyenMai dto_product = new DTO_KhuyenMai();
                dto_product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                dto_product.TenKM = dr[1].ToString();   //Tên KM
                dto_product.DonGiaCu = int.Parse(dr[2].ToString());   //Đơn giá cũ
                dto_product.DonGiaMoi = int.Parse(dr[3].ToString());   //Đơn giá mới
                dto_product.HinhAnh = dr[4].ToString();   //Link hình ảnh
                dto_AllProducts.Add(dto_product);
            }
            return dto_AllProducts;
        }
        public List<DTO_KhuyenMai> GetDetails(string makm)
        {
            List<DTO_KhuyenMai> dto_Products = new List<DTO_KhuyenMai>();
            string queryGetDetails = "SELECT MaKM, TenKM, DonGiaCu, DonGiaMoi, MoTa, DiemNoiBat, DKSuDung, GioiThieuChiTiet, HinhAnh FROM [KHUYENMAI] WHERE ([MaKM] = '" + makm + "')";
            DataTable dt = DataProvider.ExecuteQuery(queryGetDetails);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KhuyenMai dto_product = new DTO_KhuyenMai();
                dto_product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                dto_product.TenKM = dr[1].ToString();   //Tên KM
                dto_product.DonGiaCu = int.Parse(dr[2].ToString());   //Đơn giá cũ
                dto_product.DonGiaMoi = int.Parse(dr[3].ToString());   //Đơn giá mới
                dto_product.MoTa = dr[4].ToString();
                dto_product.DiemNoiBat = dr[5].ToString();
                dto_product.DKSuDung = dr[6].ToString();
                dto_product.GioiThieuChiTiet = dr[7].ToString();
                dto_product.HinhAnh = dr[8].ToString();   //Link hình ảnh
                dto_Products.Add(dto_product);
            }
            return dto_Products;
        }
        public List<DTO_KhuyenMai> SelectTop10(string makm)
        {
            List<DTO_KhuyenMai> dto_Top10Products = new List<DTO_KhuyenMai>();
            string querySelectTop10 = "SELECT TOP 10 MaKM, TenKM, DonGiaCu, DonGiaMoi, HinhAnh FROM KHUYENMAI WHERE (Left(MaKM, 1) =LEFT ('"+makm+"', 1)) AND MaKM != '"+makm+"'";
            DataTable dt = DataProvider.ExecuteQuery(querySelectTop10);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KhuyenMai product = new DTO_KhuyenMai();
                product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                product.TenKM = dr[1].ToString();   //Tên KM
                product.DonGiaCu = int.Parse(dr[2].ToString());   //Đơn giá cũ
                product.DonGiaMoi = int.Parse(dr[3].ToString());   //Đơn giá mới
                product.HinhAnh = dr[4].ToString();   //Link hình ảnh
                dto_Top10Products.Add(product);
            }
            return dto_Top10Products;
        }
        public List<DTO_KhuyenMai> SelectInType(string type)
        {
            List<DTO_KhuyenMai> dto_Products = new List<DTO_KhuyenMai>();
            string querySelect = "SELECT [MaKM], [TenKM], [DonGiaCu], [DonGiaMoi], [HinhAnh] FROM [KHUYENMAI] WHERE LoaiKM = "+type;
            DataTable dt = DataProvider.ExecuteQuery(querySelect);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KhuyenMai product = new DTO_KhuyenMai();
                product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                product.TenKM = dr[1].ToString();   //Tên KM
                product.DonGiaCu = int.Parse(dr[2].ToString());   //Đơn giá cũ
                product.DonGiaMoi = int.Parse(dr[3].ToString());   //Đơn giá mới
                product.HinhAnh = dr[4].ToString();   //Link hình ảnh
                dto_Products.Add(product);
            }
            return dto_Products;
        }
        public List<DTO_KhuyenMai> SelectInType(string type,int khuvuc)
        {
            List<DTO_KhuyenMai> dto_Products = new List<DTO_KhuyenMai>();
            string querySelect = "SELECT [MaKM], [TenKM], [DonGiaCu], [DonGiaMoi], [HinhAnh] FROM [KHUYENMAI] WHERE LoaiKM = " + type+" and KhuVuc=" + khuvuc.ToString();
            DataTable dt = DataProvider.ExecuteQuery(querySelect);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_KhuyenMai product = new DTO_KhuyenMai();
                product.MaKM = int.Parse(dr[0].ToString());   //Ma KM
                product.TenKM = dr[1].ToString();   //Tên KM
                product.DonGiaCu = int.Parse(dr[2].ToString());   //Đơn giá cũ
                product.DonGiaMoi = int.Parse(dr[3].ToString());   //Đơn giá mới
                product.HinhAnh = dr[4].ToString();   //Link hình ảnh
                dto_Products.Add(product);
            }
            return dto_Products;
        }
        public string convertTrangThai(int TrangThai)
        {
            if (TrangThai == 0)
                return "Chưa Duyệt";
            else
                return "Đã Duyệt";
        }
        public string convertLoaiKM(int LoaiKM)
        {
            if (LoaiKM == 1)
                return "Ăn Uống";
            else
                if (LoaiKM == 2)
                    return "Du Lịch";
                else
                    if (LoaiKM == 3)
                        return "Giải Trí";
                    else
                        if (LoaiKM == 4)
                            return "Thời Trang";
                        else
                            if (LoaiKM == 5)
                                return "Công Nghệ";
                            else
                                if (LoaiKM == 6)
                                    return "Làm Đẹp";
                                else
                                    if (LoaiKM == 7)
                                        return "Gia Dụng";
                                    else
                                        if (LoaiKM == 8)
                                            return "Đào Tạo";
                                        else
                                            return "Khác";
        }
        public bool AdThemTinKM(DTO_KhuyenMai km)
        {
            try
            {
                string queryInsert = "INSERT INTO KHUYENMAI(LoaiKM,TenKM,DonGiaCu,DonGiaMoi,BatDauKM,KetThucKM, MoTa, DiemNoiBat,DKSuDung,GioiThieuChiTiet,DoanhNghiep,HinhAnh,ClipQuangCao,HinhThucGiaoSP,TinhTrang)"
                                    + "VALUES (";
                queryInsert += (km.LoaiKM.ToString() + ",");
                if (km.TenKM != null)
                    queryInsert += ("N'" + km.TenKM + "', ");
                else
                    queryInsert += "null, ";
                queryInsert += (km.DonGiaCu.ToString() + ", ");
                queryInsert += (km.DonGiaCu.ToString() + ", ");
                if (km.BatDauKM != null)
                    queryInsert += ("'" + km.BatDauKM.ToString() + "', ");
                else
                    queryInsert += "null, ";
                if (km.KetThucKM != null)
                    queryInsert += ("'" + km.KetThucKM.ToString() + "', ");
                else
                    queryInsert += "null, ";
                if (km.MoTa != null)
                    queryInsert += ("N'" + km.MoTa + "', ");
                else
                    queryInsert += "null, ";
                if (km.DiemNoiBat != null)
                    queryInsert += ("N'" + km.DiemNoiBat + "', ");
                else
                    queryInsert += "null, ";
                if (km.DKSuDung != null)
                    queryInsert += ("N'" + km.DKSuDung + "', ");
                else
                    queryInsert += "null, ";
                if (km.GioiThieuChiTiet != null)
                    queryInsert += ("N'" + km.GioiThieuChiTiet + "', ");
                else
                    queryInsert += "null, ";
                queryInsert += (km.DoanhNghiep.ToString() + ", ");
                if (km.HinhAnh != null)
                    queryInsert += ("N'" + km.HinhAnh + "', ");
                else
                    queryInsert += "null, ";
                if (km.ClipQuangCao != null && km.ClipQuangCao != "")
                    queryInsert += ("N'" + km.ClipQuangCao + "', ");
                else
                    queryInsert += "null, ";
                queryInsert += (km.HinhThucGiaoSP.ToString() + ", ");
                queryInsert += (km.HinhThucGiaoSP.ToString() + ")");
                DataProvider.ExecuteQuery(queryInsert);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool ThemTinKM(int loaiKM, string tenKM, int donGiaCu, int donGiaMoi,string bd, string kt,string moTa, int doanhNghiep, string noiBat, string dksd, string chiTiet,string hinhAnh, string video, int hinhThucGiao , int tinhTrang ,int khuVuc,List<string> dsAnh)
        {
            try
            {
                string queryInsert = "INSERT INTO KHUYENMAI(LoaiKM,TenKM,DonGiaCu,DonGiaMoi,BatDauKM,KetThucKM, MoTa, DiemNoiBat,DKSuDung,GioiThieuChiTiet,DoanhNghiep,HinhAnh,ClipQuangCao,HinhThucGiaoSP,TinhTrang,KhuVuc)"
                                    + "VALUES (";               
                queryInsert += (loaiKM.ToString()+ ",");
                if (tenKM != null)
                    queryInsert += ("N'" + tenKM + "', ");
                else
                    queryInsert += "null, ";
                queryInsert += (donGiaCu.ToString() + ", ");
                queryInsert += (donGiaMoi.ToString() + ", ");
                if (bd != null)
                    queryInsert += ("'"+ bd.ToString() + "', ");
                else
                    queryInsert += "null, ";
                if (kt != null)
                    queryInsert += ("'" + kt.ToString() + "', ");
                else
                    queryInsert += "null, ";
                if (moTa != null)
                    queryInsert += ("N'" + moTa + "', ");
                else
                    queryInsert += "null, ";
                if (noiBat != null)
                    queryInsert += ("N'" + noiBat + "', ");
                else
                    queryInsert += "null, ";
                if (dksd != null)
                    queryInsert += ("N'" + dksd + "', ");
                else
                    queryInsert += "null, ";
                if (chiTiet != null)
                    queryInsert += ("N'" + chiTiet + "', ");
                else
                    queryInsert += "null, ";
                queryInsert += (doanhNghiep.ToString() + ", ");
                if (hinhAnh != null)
                    queryInsert += ("N'" + hinhAnh + "', ");
                else
                    queryInsert += "null, ";
                if (video != null&&video!="")
                    queryInsert += ("N'" + video+ "', ");
                else
                    queryInsert += "null, ";
                queryInsert += (hinhThucGiao.ToString() + ", ");
                queryInsert += (tinhTrang.ToString() + ",");
                queryInsert += (khuVuc.ToString() + ")");
                DataProvider.ExecuteQuery(queryInsert);
                string query = "Select Max (MaKM) From KHUYENMAI";
                DataTable dt = DataProvider.ExecuteQuery(query);
                int makm=int.Parse(dt.Rows[0][0].ToString());
                for (int i = 0; i < dsAnh.Count; i++)
                {
                    try
                    {   string queryInsertImage = "INSERT INTO HINHANH(KhuyenMai, HinhAnh) VALUES (";
                        queryInsertImage += makm.ToString();
                        queryInsertImage += ",'";
                        queryInsertImage += dsAnh[i];
                        queryInsertImage += "')";
                        DataProvider.ExecuteQuery(queryInsertImage);
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool UpdateTinKM(DTO_KhuyenMai km)
        {
            //string query = "UPDATE KHUYENMAI SET DienThoai='" + km.DienThoai + "', HoTen=N'" + km.HoTen + "', GioiTinh='" + km.GioiTinh + "',NgaySinh='" + km.NgaySinh + "' WHERE ([MaTK] = '" + km.MaTK + "')";
            string query = "UPDATE KHUYENMAI SET MaKM='" + km.MaKM.ToString() 
                + "', LoaiKM='" 
                + km.LoaiKM.ToString() + "', TenKM=N'" 
                + km.TenKM + "',DonGiaCu='" 
                + km.DonGiaCu.ToString() + "',DonGiaCu='"
                + km.DonGiaMoi.ToString() + "',DonGiaMoi='"
                + km.BatDauKM.ToString() + "',BatDauKM='"
                + km.KetThucKM.ToString() + "',KetThucKM='"
                + km.MoTa + "',MoTa='"
                + km.DiemNoiBat + "',DiemNoiBat='"
                + km.DKSuDung + "',DKSuDung='"
                + km.GioiThieuChiTiet + "',GioiThieuChiTiet='"
                + km.DoanhNghiep.ToString() + "',DoanhNghiep='"
                + km.HinhAnh + "',HinhAnh='"
                + km.ClipQuangCao + "',ClipQuangCao='"
                + km.HinhThucGiaoSP.ToString() + "',HinhThucGiaoSP='"
                + km.TinhTrang.ToString() + "',TinhTrang='" 
                + "' WHERE ([MaKM] = '" + km.MaKM + "')";
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
